# 数据分析题目解答(建议先赞后看，养成习惯 如果不赞，先拉出去枪毙两分钟 作者：小匠IT)
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib import font_manager as fm
import os

# 确保输出文件夹存在
output_dir = 'output/13'
os.makedirs(output_dir, exist_ok=True)

# 读取Excel文件
file_path = 'data/13/竞品SKU数据分析-原始数据(2).xlsx'
data = pd.read_excel(file_path)

# 定义字体路径并创建FontProperties对象
font_path = 'fonts/SIMSUN.TTC'  # 请根据实际情况调整路径
prop = fm.FontProperties(fname=font_path)

# 查看前几行数据以了解数据结构
print(data.head())

# 提取品类信息
data['品类'] = data['SKU'].str.slice(start=6, stop=9)

# 提取规格信息
data['规格'] = data['SKU'].str.slice(stop=-6)

# 按品类分组并统计下单数量
category_sales = data.groupby('品类')['下单数量'].sum().reset_index()

# 找出最受欢迎的品类
most_popular_category = category_sales.loc[category_sales['下单数量'].idxmax()]

# 按规格分组并统计下单数量
spec_sales = data.groupby('规格')['下单数量'].sum().reset_index()

# 找出最受欢迎的规格
most_popular_spec = spec_sales.loc[spec_sales['下单数量'].idxmax()]

# 创建新的DataFrame用于输出结果
result_data = {
    '品类': [most_popular_category['品类']],
    '下单数量': [most_popular_category['下单数量']],
    '规格': [most_popular_spec['规格']],
    '规格下单数量': [most_popular_spec['下单数量']]
}

result_df = pd.DataFrame(result_data)

# 输出结果到Excel文件
output_file_path = os.path.join(output_dir, '竞品SKU数据分析结果.xlsx')
with pd.ExcelWriter(output_file_path) as writer:
    result_df.to_excel(writer, sheet_name='分析结果', index=False)
    
    # 将终端输出的数据也写入Excel的新工作表
    summary_data = {
        '最受客户欢迎的品类': [f"{most_popular_category['品类']}"],
        '品类下单数量': [f"{most_popular_category['下单数量']}"],
        '最受客户欢迎的规格': [f"{most_popular_spec['规格']}"],
        '规格下单数量': [f"{most_popular_spec['下单数量']}"]
    }
    summary_df = pd.DataFrame(summary_data)
    summary_df.to_excel(writer, sheet_name='总结', index=False)

# 绘制图表
plt.figure(figsize=(14, 7))

# 设置全局字体属性
plt.rcParams['font.sans-serif']=['SimSun']  # 中文宋体
plt.rcParams['axes.unicode_minus']=False  # 解决负号 '-' 显示为方块的问题

# 绘制品类下单数量柱状图
ax1 = plt.subplot(1, 2, 1)
category_sales.plot(kind='bar', x='品类', y='下单数量', color='skyblue', ax=ax1)
plt.title('不同品类的下单数量', fontproperties=prop)
plt.xlabel('品类', fontproperties=prop)
plt.ylabel('下单数量', fontproperties=prop)

# 添加数据标签
for p in ax1.patches:
    ax1.text(p.get_x() + p.get_width()/2., p.get_height(), 
             f'{int(p.get_height())}', ha='center', va='bottom', fontproperties=prop)

# 绘制规格下单数量柱状图
ax2 = plt.subplot(1, 2, 2)
spec_sales.plot(kind='bar', x='规格', y='下单数量', color='lightgreen', ax=ax2)
plt.title('不同规格的下单数量', fontproperties=prop)
plt.xlabel('规格', fontproperties=prop)
plt.ylabel('下单数量', fontproperties=prop)

# 添加数据标签
for p in ax2.patches:
    ax2.text(p.get_x() + p.get_width()/2., p.get_height(), 
             f'{int(p.get_height())}', ha='center', va='bottom', fontproperties=prop)

# 调整布局以防止重叠
plt.tight_layout()

# 保存图表到文件
chart_output_path = os.path.join(output_dir, '竞品SKU数据分析图表.png')
plt.savefig(chart_output_path, dpi=300, bbox_inches='tight')  # 保存图表为图片文件

# 显示图表
plt.show()